package com.example.demo.mapper;

import com.example.demo.mapper.entity.StudentCouponsView;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface CouponMonitorMapper {
    @Select("select count(*) from student_coupons_view where user_id = #{userId} and coupons_type = #{type} and student_coupons_status=2")
    int getUsedCoupons(@Param("userId") int userId, @Param("type") int type);

    @Select("select count(*) from student_coupons_view where user_id = #{userId} and coupons_type = #{type}")
    int getCouponsCount(@Param("userId") int userId, @Param("type") int type);

    @Select("select * from student_coupons_view")
    List<StudentCouponsView> getCouponsView();

    @Select("select count(*) from student_coupons_view where user_id = #{userId} ")
    int getCouponsTotalCount(int userId);

    @Select("select count(*) from student_coupons_view where user_id = #{userId} and student_coupons_status = 2")
    int getUsedCouponsNum(int userId);

    @Select("SELECT coupons_type, COUNT(*) as count " +
            "FROM student_coupons_view " +
            "WHERE user_id = #{userId} " +
            "GROUP BY coupons_type " +
            "ORDER BY count DESC " +
            "LIMIT 1; ")
    int getMostUsedCoupons(int userId);

    @Select("SELECT coupons_type, COUNT(*) as count " +
            "FROM student_coupons_view " +
            "WHERE user_id = #{userId} " +
            "GROUP BY coupons_type " +
            "ORDER BY count ASC " +
            "LIMIT 1; ")
    int getLeastUsedCoupons(int userId);

    @Select("select count(*) from student_coupons_view where user_id = #{userId} and coupons_type = 1 and student_coupons_status = 2")
    int getVoucherNum(int userId);

    @Select("select count(*) from student_coupons_view where user_id = #{userId} and coupons_type = 2 and student_coupons_status = 2")
    int getFullDiscountNum(int userId);

    @Select("select count(*) from student_coupons_view where user_id = #{userId} and coupons_type = 3 and student_coupons_status = 2")
    int getDiscountNum(int userId);

    @Select("select count(*) from student_coupons_view where user_id = #{userId} and coupons_type = 4 and student_coupons_status = 2")
    int getExchangeNum(int userId);
}
